﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class ReportDao
    {
        public bool Delete(SqlConnection conn, int reportId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Report_Comment] ");
            builder.Append("WHERE ");
            builder.Append("\t[ReportId] IN ( ");
            builder.Append("\t\t\tSELECT ");
            builder.Append("\t\t\t\t[ReportId] ");
            builder.Append("\t\t\tFROM ");
            builder.Append("\t\t\t\t[dbo].[Report] ");
            builder.Append("\t\t\tWHERE ");
            builder.Append("\t\t\t\t[ReportId] = @ReportId ");
            builder.Append("\t\t\t\tAND [UserId] = @UserId ");
            builder.Append("\t\t\t) ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Attachment] ");
            builder.Append("SET ");
            builder.Append("\t[DeleteFlag] = 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[AttachmentId] IN ");
            builder.Append("\t\t\t( ");
            builder.Append("\t\t\t\tSELECT ");
            builder.Append("\t\t\t\t\t[AttachmentId] ");
            builder.Append("\t\t\t\tFROM ");
            builder.Append("\t\t\t\t\t[dbo].[Report] ");
            builder.Append("\t\t\t\tWHERE ");
            builder.Append("\t\t\t\t\t[ReportId] = @ReportId\t ");
            builder.Append("\t\t\t\t    AND [UserId] = @UserId ");
            builder.Append("\t\t\t\t\tAND ISNULL([AttachmentId],'') <> ''\t");
            builder.Append("\t\t\t) ");
            builder.Append("DELETE FROM [dbo].[Report] ");
            builder.Append("WHERE ");
            builder.Append("\t[ReportId] = @ReportId ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@ReportId", SqlDbType.Int);
            commandParameters[0].Value = reportId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, ReportData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Report] ");
            builder.Append("( ");
            builder.Append("\t[Title], ");
            builder.Append("\t[Type], ");
            builder.Append("\t[ReportTime], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[Note1], ");
            builder.Append("\t[Note2], ");
            builder.Append("\t[Note3], ");
            builder.Append("\t[Note4], ");
            builder.Append("\t[Managers], ");
            builder.Append("\t[AttachmentId], ");
            builder.Append("\t[AddTime] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@Title, ");
            builder.Append("\t@Type, ");
            builder.Append("\t@ReportTime, ");
            builder.Append("\t@UserId, ");
            builder.Append("\t@Note1, ");
            builder.Append("\t@Note2, ");
            builder.Append("\t@Note3, ");
            builder.Append("\t@Note4, ");
            builder.Append("\t@Managers, ");
            builder.Append("\t@AttachmentId, ");
            builder.Append("\t@AddTime ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[11];
            commandParameters[0] = new SqlParameter("@Title", SqlDbType.NVarChar, 200);
            commandParameters[0].Value = data.Title;
            commandParameters[1] = new SqlParameter("@Type", SqlDbType.Int);
            commandParameters[1].Value = data.Type;
            commandParameters[2] = new SqlParameter("@ReportTime", SqlDbType.DateTime);
            commandParameters[2].Value = data.ReportTime;
            commandParameters[3] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[3].Value = data.UserId;
            commandParameters[4] = new SqlParameter("@Note1", SqlDbType.NText);
            if (data.Note1IsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Note1;
            }
            commandParameters[5] = new SqlParameter("@Note2", SqlDbType.NText);
            if (data.Note2IsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.Note2;
            }
            commandParameters[6] = new SqlParameter("@Note3", SqlDbType.NText);
            if (data.Note3IsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.Note3;
            }
            commandParameters[7] = new SqlParameter("@Note4", SqlDbType.NText);
            if (data.Note4IsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Note4;
            }
            commandParameters[8] = new SqlParameter("@Managers", SqlDbType.NText);
            if (data.ManagersIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.Managers;
            }
            commandParameters[9] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.AttachmentId;
            }
            commandParameters[10] = new SqlParameter("@AddTime", SqlDbType.DateTime);
            commandParameters[10].Value = data.AddTime;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectByReportId(SqlConnection conn, int reportId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Report].*, ");
            builder.Append("\tdbo.GetUserNames(CAST([Report].[Managers] AS nvarchar(4000))) AS ManagerNames, ");
            builder.Append("\t[Department].[DeptName], ");
            builder.Append("\t[Users].[UserName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Report], ");
            builder.Append("\t[dbo].[Department], ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\t[Report].[ReportId] = @ReportId ");
            builder.Append("\tAND [Report].[UserId] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@ReportId", SqlDbType.Int) };
            commandParameters[0].Value = reportId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Report";
            return set.Tables[0];
        }

        public DataTable SelectManagePaged(SqlConnection conn, int pagesize, int pageindex, bool docount, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([ReportId]) FROM [dbo].[Report] WHERE CHARINDEX(','+ @UserId + ',',',' + ISNULL(CAST([Managers] AS nvarchar(2000)) ,'') + ',') > 0 ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [ReportId] FROM [dbo].[Report] WHERE CHARINDEX(','+ @UserId + ',',',' + ISNULL(CAST([Managers] AS nvarchar(2000)) ,'') + ',') > 0 ORDER BY [AddTime] DESC, [ReportId] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[Report].[ReportId], ");
            builder.Append("\t\t\t[Report].[Title], ");
            builder.Append("\t\t\t[Report].[Type], ");
            builder.Append("\t\t\t[Report].[ReportTime], ");
            builder.Append("\t\t\t[Users].[UserName], ");
            builder.Append("\t\t\t[Department].[DeptName], ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Report], ");
            builder.Append("\t\t\t[dbo].[Users], ");
            builder.Append("\t\t\t[dbo].[Department], ");
            builder.Append("\t\t\t@IndexTable t\t\t\t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[Report].[UserId] = [Users].[UserId] ");
            builder.Append("\t\t\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("\t\t\tAND [Report].[ReportId] = t.PK\t\t ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[AddTime] DESC, ");
            builder.Append("\t\t\t[ReportId] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            commandParameters[3] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[3].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Report";
            return set.Tables[0];
        }

        public DataTable SelectPaged(SqlConnection conn, int pagesize, int pageindex, bool docount, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([ReportId]) FROM [dbo].[Report] WHERE [UserId] = @UserId ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [ReportId] FROM [dbo].[Report] WHERE [UserId] = @UserId ORDER BY [ReportTime] DESC, [ReportId] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[Report].[ReportId], ");
            builder.Append("\t\t\t[Report].[Title], ");
            builder.Append("\t\t\t[Report].[Type], ");
            builder.Append("\t\t\t[Report].[Managers], ");
            builder.Append("\t\t\t[Report].[ReportTime], ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Report],@IndexTable t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[Report].[ReportId] = t.PK\t\t\t ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[ReportTime] DESC, ");
            builder.Append("\t\t\t[ReportId] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            commandParameters[3] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[3].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Report";
            return set.Tables[0];
        }

        public DataTable SelectTop10(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT TOP 10 ");
            builder.Append("\t[Report].[ReportId], ");
            builder.Append("\t[Report].[Title], ");
            builder.Append("\t[Report].[Type], ");
            builder.Append("\t[Report].[ReportTime], ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Department].[DeptName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Report], ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[Report].[UserId] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("\tAND [Users].[UserId] = @UserId ");
            builder.Append("ORDER BY ");
            builder.Append("\t[AddTime] DESC, ");
            builder.Append("\t[ReportId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Report";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, ReportData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Report] SET ");
            builder.Append("\t[Title] = @Title, ");
            builder.Append("\t[Type] = @Type, ");
            builder.Append("\t[ReportTime] = @ReportTime, ");
            builder.Append("\t[UserId] = @UserId, ");
            builder.Append("\t[Note1] = @Note1, ");
            builder.Append("\t[Note2] = @Note2, ");
            builder.Append("\t[Note3] = @Note3, ");
            builder.Append("\t[Note4] = @Note4, ");
            builder.Append("\t[Managers] = @Managers, ");
            builder.Append("\t[AttachmentId] = @AttachmentId, ");
            builder.Append("\t[AddTime] = @AddTime ");
            builder.Append("WHERE ");
            builder.Append("\t[ReportId] = @ReportId ");
            SqlParameter[] commandParameters = new SqlParameter[12];
            commandParameters[0] = new SqlParameter("@ReportId", SqlDbType.Int);
            commandParameters[0].Value = data.ReportId;
            commandParameters[1] = new SqlParameter("@Title", SqlDbType.NVarChar, 200);
            commandParameters[1].Value = data.Title;
            commandParameters[2] = new SqlParameter("@Type", SqlDbType.Int);
            commandParameters[2].Value = data.Type;
            commandParameters[3] = new SqlParameter("@ReportTime", SqlDbType.DateTime);
            commandParameters[3].Value = data.ReportTime;
            commandParameters[4] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[4].Value = data.UserId;
            commandParameters[5] = new SqlParameter("@Note1", SqlDbType.NText);
            if (data.Note1IsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.Note1;
            }
            commandParameters[6] = new SqlParameter("@Note2", SqlDbType.NText);
            if (data.Note2IsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.Note2;
            }
            commandParameters[7] = new SqlParameter("@Note3", SqlDbType.NText);
            if (data.Note3IsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Note3;
            }
            commandParameters[8] = new SqlParameter("@Note4", SqlDbType.NText);
            if (data.Note4IsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.Note4;
            }
            commandParameters[9] = new SqlParameter("@Managers", SqlDbType.NText);
            if (data.ManagersIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.Managers;
            }
            commandParameters[10] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.AttachmentId;
            }
            commandParameters[11] = new SqlParameter("@AddTime", SqlDbType.DateTime);
            commandParameters[11].Value = data.AddTime;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

